package dao

import (
	"database/sql"
	"errors"

	"github.com/ammyhaber/seckill/service/inventory/model"
	"github.com/jmoiron/sqlx"
)

type InventoryDAO struct {
	db *sqlx.DB
}

func NewInventoryDAO(db *sqlx.DB) *InventoryDAO {
	return &InventoryDAO{db: db}
}

// GetDB 获取数据库连接
func (d *InventoryDAO) GetDB() *sqlx.DB {
	return d.db
}

// GetByProductID 根据商品ID获取库存
func (d *InventoryDAO) GetByProductID(productID int64) (*model.Inventory, error) {
	var inventory model.Inventory
	sqlQuery := "SELECT * FROM inventory WHERE product_id = ?"
	err := d.db.Get(&inventory, sqlQuery, productID)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}
	return &inventory, nil
}

// DecrByProductID 扣减库存（乐观锁）
func (d *InventoryDAO) DecrByProductID(productID int64, count int, version int) (bool, error) {
	sql := `UPDATE inventory SET available = available - ?, locked = locked + ?, version = version + 1 
		   WHERE product_id = ? AND version = ? AND available >= ?`
	result, err := d.db.Exec(sql, count, count, productID, version, count)
	if err != nil {
		return false, err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return false, err
	}
	return affected > 0, nil
}

// GetInventoryDetailList 获取库存详情列表
func (d *InventoryDAO) GetInventoryDetailList() ([]*model.InventoryDetail, error) {
	sql := `SELECT i.*, p.name as product_name, p.price as product_price 
		   FROM inventory i 
		   LEFT JOIN product p ON i.product_id = p.id`
	var details []*model.InventoryDetail
	err := d.db.Select(&details, sql)
	if err != nil {
		return nil, err
	}
	return details, nil
}

// UnlockByProductID 解锁库存
func (d *InventoryDAO) UnlockByProductID(productID int64, count int) error {
	sql := "UPDATE inventory SET locked = locked - ?, available = available + ? WHERE product_id = ?"
	_, err := d.db.Exec(sql, count, count, productID)
	return err
}

// UnlockByProductIDWithTx 使用事务解锁库存
func (d *InventoryDAO) UnlockByProductIDWithTx(tx *sqlx.Tx, productID int64, count int) error {
	sql := "UPDATE inventory SET locked = locked - ?, available = available + ? WHERE product_id = ?"
	_, err := tx.Exec(sql, count, count, productID)
	return err
}

// ConfirmDeductionByProductID 确认扣减库存
func (d *InventoryDAO) ConfirmDeductionByProductID(productID int64, count int) error {
	sql := "UPDATE inventory SET locked = locked - ? WHERE product_id = ? AND locked >= ?"
	_, err := d.db.Exec(sql, count, productID, count)
	return err
}

// ConfirmDeductionByProductIDWithTx 使用事务确认扣减库存
func (d *InventoryDAO) ConfirmDeductionByProductIDWithTx(tx *sqlx.Tx, productID int64, count int) error {
	sql := "UPDATE inventory SET locked = locked - ? WHERE product_id = ? AND locked >= ?"
	_, err := tx.Exec(sql, count, productID, count)
	return err
}

// ExecUpdateInventory 执行库存更新SQL
func (d *InventoryDAO) ExecUpdateInventory(available, locked int, version int64, productID int64, oldVersion int64) (int64, error) {
	query := "UPDATE inventory SET available = ?, locked = ?, version = ? WHERE product_id = ? AND version = ?"
	result, err := d.db.Exec(query, available, locked, version, productID, oldVersion)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

// ExecUpdateInventoryWithTx 使用事务执行库存更新SQL
func (d *InventoryDAO) ExecUpdateInventoryWithTx(tx *sqlx.Tx, available, locked int, version int64, productID int64, oldVersion int64) (int64, error) {
	query := "UPDATE inventory SET available = ?, locked = ?, version = ? WHERE product_id = ? AND version = ?"
	result, err := tx.Exec(query, available, locked, version, productID, oldVersion)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

// GetInventoryDetailByProductID 根据商品ID获取库存详情
func (d *InventoryDAO) GetInventoryDetailByProductID(productID int64) (*model.InventoryDetail, error) {
	query := `SELECT i.*, p.name as product_name, p.price as product_price 
		   FROM inventory i 
		   LEFT JOIN product p ON i.product_id = p.id
		   WHERE i.product_id = ?`
	var detail model.InventoryDetail
	err := d.db.Get(&detail, query, productID)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}
	return &detail, nil
}
